package org.poem.service.impl;

import com.google.common.collect.Lists;
import org.poem.datasource.table.SchemaVO;
import org.poem.datasource.table.TableVO;
import org.poem.service.GatherDataBaseInter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * 数据结构
 * 获取数据库
 * 并且获取数据库表
 *
 * @author Administrator
 */
public abstract class AbstractGatherDataBaseInter implements GatherDataBaseInter {

    /**
     *
     */
    private static final Logger logger = LoggerFactory.getLogger(AbstractGatherDataBaseInter.class);


    /**
     * @param connection
     * @return
     */
    @Override
    public List<TableVO> getDataBases(Connection connection,String sourceType,String sourceSchema) {
        if (connection != null) {
            return  getAllTableName(sourceSchema,  connection, sourceType);
        }
        return Lists.newArrayList();
    }


    /**
     * 获取数据
     *
     * @param connection
     * @return
     */
    @Override
    public List<String> getSchema(Connection connection,String sourceType) {
        List<String> databases = Lists.newArrayList();
        if (connection != null) {
            try {
                Statement stmt = connection.createStatement();
                String sql;
                if ("mysql".equalsIgnoreCase(sourceType)){
                    sql = "show databases ";
                }else{
                    sql = "select name from master.dbo.SysDatabases ";
                }
                ResultSet res = stmt.executeQuery(sql);
                while (res.next()) {
                    String schemaName = res.getString(1);
                    if (!"performance_schema".equalsIgnoreCase(schemaName)
                            && !"mysql".equalsIgnoreCase(schemaName)
                            && !"information_schema".equalsIgnoreCase(schemaName)
                            && !"sys".equalsIgnoreCase(schemaName)
                            && !"test".equalsIgnoreCase(schemaName)
                            && !"tempdb".equalsIgnoreCase(schemaName)
                            && !"model".equalsIgnoreCase(schemaName)
                            && !"msdb".equalsIgnoreCase(schemaName)
                            && !"ReportServer".equalsIgnoreCase(schemaName)
                            && !"ReportServerTempDB".equalsIgnoreCase(schemaName)) {
                        databases.add(schemaName);
                    }
                }
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
                e.printStackTrace();
            }
        }
        return databases;
    }

    /**
     * 关闭
     *
     * @param pstmt
     * @param rs
     */
    private static void release(Statement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                logger.debug("关闭存储查询结果的ResultSet对象");
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                logger.debug("关闭负责执行SQL命令的Statement对象");
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取所有的表
     *
     * @param db
     * @param connection
     * @return
     */
    private List<TableVO> getAllTableName(String sourceSchema, Connection connection,String sourceType) {
        Statement stmt = null;
        ResultSet res = null;
        if (connection == null) {
            return Lists.newArrayList();
        }
        List<TableVO> tableEntities = Lists.newArrayList();
        try {
            boolean cl = connection.isClosed();
            if (!cl) {
                stmt = connection.createStatement();
                String sql = "";
                if ("mysql".equalsIgnoreCase(sourceType)){
                    sql = "show tables from  " + sourceSchema ;
                }else if ("sqlserver".equalsIgnoreCase(sourceType)){
                    sql = "select name from sysobjects where xtype='U'";
                }
                res = stmt.executeQuery(sql);
                while (res.next()) {
                    TableVO tableEntity = new TableVO();
                    tableEntity.setName(res.getString(1));
                    tableEntities.add(tableEntity);
                }

            }

        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            e.printStackTrace();
        } finally {
            release(stmt, res);
        }
        return tableEntities;
    }
}

